#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
import logger


db = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8' )

def getallmess(str):
    logger.loggerprint('getmess' + "   " + str)
    cursor = db.cursor()
    sql = 'select * from %s' % str
    logger.loggerprint(sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    #print(result)
    logger.loggerprint(result)
    return result

def getmessbystr(tablename,index,mess,orderby):
    cursor = db.cursor()
    sql = 'select * from %s where %s = \'%s\' order by %s'%(tablename,index,mess,orderby)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

def getmessbyid(tablename,index,mess,orderby):
    cursor = db.cursor()
    sql = 'select * from %s where %s = %d order by %s'%(tablename,index,mess,orderby)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

def getcollist(str):
    list = []
    logger.loggerprint('getcol' + "   " + str)
    cursor = db.cursor()
    sql = 'SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name= \'%s\'' % str
    logger.loggerprint(sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    logger.loggerprint(result)
    for i in result:
        list.append(i[0])
    return list

def getno(str):
    cursor = db.cursor()
    sql = 'select 部门序号 from 部门表 where 部门名称 = \'%s\' ' % str
    cursor.execute(sql)
    result = cursor.fetchall()
    return result[0][0]

def dbclose():
    db.close()



"""
合并表时的代码
tablenamelist = getmess(table_name1)

sum = 0

for i in range(len(tablenamelist)):
    tablename = tablenamelist[i][1]
    print(tablename)
    print(getno(tablename))
    num = getno(tablename)[0][0]
    tablemess = getmess(tablenamelist[i][1])
    print(tablemess)
    print(len(tablemess))
    sum += len(tablemess)
    for j in range(len(tablemess)):
        print(tablemess[j][0])
        print(tablemess[j][1])

        if not tablemess[j][1] == None:
            sql = 'insert into %s(姓名,职务,部门ID) values(\'%s\',\'%s\',%d)' % (table_name, tablemess[j][0], tablemess[j][1],num)
        else :
            sql = 'insert into %s(姓名,部门ID) values(\'%s\',%d)' %(table_name,tablemess[j][0],num)
        print(sql)
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
"""



